<?php
include_once('JSON.php');

 Class Foxydb{ //FoxydbMysql
 
	/*** mysql hostname ***/
	var $dbhost = 'localhost';

	/*** mysql username ***/
	var $dbuser = 'ezqw_root';//'ezqw_root';

	/*** mysql password ***/
	var $dbpass = '15317227';

	var $dbname = 'ezqw_database';//'ezqw_foxysite';
  
	function open()
	{
		$this->conn = mysql_connect($this->dbhost, $this->dbuser, $this->dbpass) or die ('Error connecting to mysql');

		mysql_select_db($this->dbname);

		mysql_query('SET NAMES utf8');
	}

	function close()
	{
		/*** close the database connection ***/
		mysql_close($this->conn);
	}

	function findSiteByUri($uri)
	{
		if($uri){
			$sql = " SELECT * FROM site WHERE uri='$uri'";

			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			$ret = mysql_fetch_array($rs, MYSQL_ASSOC);   

			return $ret;
		}
	}
 

	function findSiteBySid($sid)
	{
		if($sid){
			$sql = " SELECT * FROM site WHERE sid=$sid";

			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			$ret = mysql_fetch_array($rs, MYSQL_ASSOC);   

			return $ret;
		}
	}


	function findCategoryByCatSid($catsid)
	{
		if($catsid){
			$sql = " SELECT * FROM category WHERE sid=$catsid";
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			$ret = mysql_fetch_array($rs, MYSQL_ASSOC);   

			return $ret;
		}
	}

	function selectSitesByCatSid($catsid,$num=1,$start=0)
	{
		if($catsid){
			$end = $start + $num;

			$sql = "SELECT s.* FROM site s,site2category s2c WHERE s.sid=s2c.sitesid AND s2c.catsid = $catsid ORDER BY s.ranking DESC LIMIT $start, $end";


			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

			while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
				$ret[] = $row;
			}

			mysql_free_result($rs);

			return $ret;
		}
	}


	function findSiteCountByCatSid($catsid)
	{
		if($catsid){
			$sql = "SELECT count(*) cnt FROM site2category  WHERE catsid = $catsid ";

			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			$ret = mysql_fetch_array($rs, MYSQL_ASSOC);   

			if($ret) {
				return $ret["cnt"];
			}
		}
		return 0;
	}

	function selectSite2CategoriesBySiteSid($sitesid)
	{
		if($sitesid){
			$sql = "SELECT * FROM site2category  WHERE sitesid = $sitesid";

			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

			while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
				$ret[] = $row;
			}

			mysql_free_result($rs);

			return $ret;
		}
	}
			
	function selectSite2CategoriesByCateSid($catsid)
    {
		if($catsid){
			$sql = "SELECT s2c.* FROM site2category s2c,site s WHERE s2c.catsid = $catsid AND s2c.sitesid = s.sid ORDER BY s.ranking DESC";			

			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

			while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
				$ret[] = $row;
			}

			mysql_free_result($rs);

			return $ret;
		}
	}

	function findCategoryPrimaryPath($catsid)
    {
		if($catsid){
			$result = array();
			$cat = $this->findCategoryByCatSid($catsid);
			$result[] = $cat;
			//Toppest category sid is 0
			$pcat = null;
			while($cat){
				$pcat = $cat;
				$cat = $this->findCategoryByCatSid($cat["psid"]);
				if($cat) $result[] = $cat;
				if($pcat == $cat) break;
			}
			return $result;
		}
	}

	function getChildCategories($catsid)
    {
		if($catsid){
			$sql = " SELECT * FROM category WHERE psid = $catsid";
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

			while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
				$ret[] = $row;
			}

			mysql_free_result($rs);

			return $ret;
		}
	}

	function registerSite($puri,$pname="",$pdesc="",$psearchpath="",$prsspath="",$primarycatsid=0,$primaryareacatsid=0,$pcontact="")
    {
		$uri = trim($puri);
		$site = $this->findSiteByUri($uri);
		if(!$site){
			$sql = "INSERT INTO site (uri,name,description,searchpath,rsspath,primarycatsid,primaryareacatsid,contact)  values('$uri','$pname','$pdesc','$psearchpath','$prsspath',$primarycatsid,$primaryareacatsid,'$pcontact')";			
			$result = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			return $result;
		}
	}

	function updateSiteProfile($sitesid,$name,$desc,$contact,$searchpath,$rsspath)
    {
		if($sitesid){
			$sql = "UPDATE site SET name='$name', description='$desc', contact='$contact', searchpath='$searchpath', rsspath='$rsspath' WHERE sid=$sitesid";			
			$result = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			return $result;
		}
	}

	function updateSitePrimaryCategory($sitesid,$ptype,$pcatsid)
    {
		if($sitesid && $ptype && $pcatsid){
			if($ptype == 'content'){
				$sql = "UPDATE site SET primarycatsid = $pcatsid WHERE sid= $sitesid";			
			}else if ($ptype == 'area'){
				$sql = "UPDATE site SET primaryareacatsid = $pcatsid WHERE sid= $sitesid";			
			}
			if($sql){
				$result = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
				return $result;
			}
		}
	}
    
    function deleteSiteFromCategoryTrail($sitesid,$pcatsid)
    {
        if($sitesid && $pcatsid){
            $cats = $this->findCategoryPrimaryPath($pcatsid);
            foreach($cats as $cat){
                $sql = "DELETE FROM site2Category WHERE sitesid=$sitesid AND catsid=".$cat['sid'];
				$result = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
            }
        }
    }
    
    function addSiteToCategoryTrail($sitesid,$pcatsid)
    {
        if($sitesid && $pcatsid){
            $cats = $this->findCategoryPrimaryPath($pcatsid);
            foreach($cats as $cat){
                $sql = "INSERT INTO site2Category (sitesid,catsid) VALUES($sitesid,".$cat['sid'].")";
				$result = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
            }
        }
    }

    function addSiteToCategory($sitesid,$pcatsid)
    {
        if($sitesid && $pcatsid){
			$sql = "SELECT count(*) cnt FROM site2Category WHERE sitesid=$sitesid AND catsid=$pcatsid";
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			$ret = mysql_fetch_array($rs, MYSQL_ASSOC);   

			if($ret && $ret["cnt"] == 0) {
				$sql = "INSERT INTO site2Category (sitesid,catsid) VALUES($sitesid,".$pcatsid.")";
				$result = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			}
        }
    }

	function findLinkLog($url,$usersid)
    {
		if ($url && $usersid){
			$sql = "SELECT * from linklog WHERE linkurl='$url' AND usersid=$usersid";
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			$ret = mysql_fetch_array($rs, MYSQL_ASSOC);   
			return $ret;
		}
	}

	function findLinkLogBySid($sid)
    {
		if ($sid){
			$sql = "SELECT * from linklog WHERE sid=$sid";
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			$ret = mysql_fetch_array($rs, MYSQL_ASSOC);   
		}
		return $ret;
	}

	function selectLinkLogByUserSid($usersid,$num=1,$start=0)
    {
		$end = $start + $num;

		if ($usersid){
			$sql = "SELECT * from linklog WHERE usersid=$usersid ORDER BY createdDate DESC LIMIT $start, $end";
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

			while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
				$ret[] = $row;
			}

			mysql_free_result($rs);

			return $ret;
		}
		return $ret;
	}

	/**
	* $tags string seperated by comma
	*/
	function createLinkTags($ptags){
		$tags = explode(",",$ptags);
        $tags = array_unique($tags);
        foreach($tags as $tag){
            $t = $this->findTagByName($tag);
            if(!$t){
                $sql = "INSERT INTO tag (name,psid) VALUES('$tag',0)";
			    $rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());                
            }
        }
	}
    
	function findTagByName($name){
        $sql = "SELECT * from tag where name = '$name'";
		$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
		$ret = mysql_fetch_array($rs, MYSQL_ASSOC);   

		return $ret;
    }

	function findTagBySid($tagsid){
        $sql = "SELECT * from tag where sid = $tagsid";
		$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
		$ret = mysql_fetch_array($rs, MYSQL_ASSOC);   

		return $ret;
    }


	function saveLinkLog($url,$usersid)
    {        
        //save linklog
		$linklog = $this->findLinkLog($url,$usersid);

		if(!$linkLog){
			// Assuming the above tags are at www.example.com
			$tags = get_meta_tags($url);

			// Notice how the keys are all lowercase now, and
			// how . was replaced by _ in the key.
			$title = getHtmlTitle($url);
			if($tags['keywords']) $tags = implode(",",$tags['keywords']);
			$desc = $tags['description'];

			$utag = implode(",",array_unique(explode(",",$tags)));
		
			$sql = "INSERT INTO linklog(linkurl,linkname,tag,description,linktype,usersid,status ) VALUES('$url', '$title', '$utag', '$desc',0, $usersid,0)";
			$result = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

			//save link
			$this->saveLink($url,$title,$desc,0);

			//create tag
			$this->createLinkTags($tag);

			//after saved, find it and then create link2tag
			$linklog = $this->findLinkLog($url,$usersid);
			if($linklog) {
				$this->createLink2TagFromLog($linklog['sid']);
				//print_r($linklog);
				//$this->increaseViewCount($linklog['linkurl']);
			}
		}
	}
	function increaseViewCount($url)
    {
		$link = $this->findLinkByUrl($url);
		$sql = "UPDATE linksummary SET viewcount=viewcount+1 WHERE linksid=".$link['sid'];
		$result = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
	}

	function updateLinkLog($url,$name,$tag,$desc,$type,$usersid)
    {
		$sql = "UPDATE linklog SET linkname='$name',tag='$tag',description='$desc',linktype=$type WHERE linkurl='$url' AND usersid=$usersid ";
		$result = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
	}

	function saveLink($url,$name,$desc,$type=1)
    {
		$link = $this->findLinkByUrl($url);
		if (!$link){
			$sql = "INSERT INTO link(url,name,description,linktype) VALUES('$url', '$name', '$desc',$type)";
			$result = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
		}else{
			$sql = "UPDATE link SET name='$name',description='$desc',linktype=$type WHERE sid=".$link['sid'];
			//echo "saveLink sql=$sql";
			$result = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
		}
	}

	function findLinkByUrl($url)  
	{
		if($url){
			$sql = " SELECT * FROM link WHERE url='$url'";
			//echo "<br>findLinkByUrl--------1 sql=$sql";
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			$ret = mysql_fetch_array($rs, MYSQL_ASSOC);   

			return $ret;
		}
	}
 
	/**
	* $sid is linklog sid
	*/
    function createLink2TagFromLog($sid)
	{
		$now = time();
		$linklog = $this->findLinkLogBySid($sid);
		if($linklog){
			$link = $this->findLinkByUrl($linklog['linkurl']);
			if($link){
				//add link2tag 
				$difftags = array_unique(array_diff(explode(",",$linklog['tag']), explode(",",$linklog['computedtag'])));
				//echo "createLink2TagFromLog-------------1<br>";
				//print_r($difftags);
				foreach($difftags as $tagname){
					$tag = $this->findTagByName($tagname);
					if($tag){
						$link2tag = $this->findLink2Tag($link['sid'],$tag['sid']);
						if($link2tag){
							$sql = "UPDATE link2tag SET ranking= ranking+1, lastupdateddate='".gmdate('Y-m-d H:i:s', $now)."' WHERE  sid=" . $link2tag['sid'];
						}else{
							$sql = "INSERT link2tag (linksid,tagsid,ranking,createddate,lastupdateddate) VALUES(".$link['sid'].",". $tag['sid'].",1,'".gmdate('Y-m-d H:i:s', $now)."','".gmdate('Y-m-d H:i:s', $now)."')";
						}
						$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
					}
				}
				//remove link2tag
				$difftags = array_unique(array_diff(explode(",",$linklog['computedtag']) , explode(",",$linklog['tag'])));
				//echo "<br>createLink2TagFromLog-------------2<br>";
				//print_r($difftags);
				foreach($difftags as $tagname){
					$tag = $this->findTagByName($tagname);
					if($tag){
						$link2tag = $this->findLink2Tag($link['sid'],$tag['sid']);
						if($link2tag){
							$sql = "UPDATE link2tag SET ranking= ranking-1, lastupdateddate='".gmdate('Y-m-d H:i:s', $now)."' WHERE  sid=". $link2tag['sid'];
							$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
						}
					}
				}


				//update linklog
				$sql = "UPDATE linklog SET computedtag=tag, lastupdateddate='".gmdate('Y-m-d H:i:s', $now)."' WHERE sid=" . $linklog['sid'];
				$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			}
		}
    }


	function findLink2Tag($linksid,$tagsid)
	{

		if($linksid && $tagsid){
			$sql = "SELECT * FROM link2tag where linksid=$linksid AND tagsid=$tagsid";

			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			$ret = mysql_fetch_array($rs, MYSQL_ASSOC);   
			return $ret;
		}
	}


	function selectHotTagsByUrl($purl,$pnum=10)
	{
		$start = 0;
		$end = $pnum;
		$link = $this->findLinkByUrl($purl);
		if($link){
			$sql = "SELECT t.* FROM link2tag lnt,tag t WHERE lnt.linksid=".$link['sid']." AND lnt.tagsid=t.sid  ORDER BY lnt.ranking DESC LIMIT $start, $end";
			//echo "<br>selectHotTagsByUrl sql=$sql";
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

			while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
				$ret[] = $row;
			}

			mysql_free_result($rs);
			//print_r($ret);
			return $ret;
		}
	}

	function selectHotTagsByTagsid($ptagsid,$pnum=10)
	{
		$start = 0;
		$end = $pnum;
		$tag = $this->findTagBySid($ptagsid);
		if($tag){
			$sql = "SELECT * FROM tag WHERE psid=".$tag['psid']."  ORDER BY ranking DESC LIMIT $start, $end";
			//echo "<br>selectHotTagsByUrl sql=$sql";
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

			while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
				$ret[] = $row;
			}

			mysql_free_result($rs);
			//print_r($ret);
			return $ret;
		}
	}

	function selectHotTags($pnum=10)
	{
		$start = 0;
		$end = $pnum;

			$sql = "SELECT * FROM tag ORDER BY ranking DESC LIMIT $start, $end";
			//echo "<br>selectHotTags sql=$sql";
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

			while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
				$ret[] = $row;
			}

			mysql_free_result($rs);
			//print_r($ret);
			return $ret;
	}

	function selectHotLinksByTagSid($tagsid,$pnum=10)
	{
		$start = 0;
		$end = $pnum;
		$tag = $this->findTagBySid($tagsid);
		if($tag){
			$sql = "SELECT ln.* FROM  link2tag lnt, link ln WHERE lnt.tagsid=".$tag['sid']." AND lnt.linksid=ln.sid  ORDER BY lnt.ranking DESC LIMIT $start, $end";
			//echo "<br>selectHotLinksByTagSid sql=$sql";
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

			while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
				$ret[] = $row;
			}

			mysql_free_result($rs);
			//print_r($ret);
			return $ret;
		}
	}


	function selectHotLinks($pnum=10)
	{
		$start = 0;
		$end = $pnum;

		$sql = "SELECT * FROM  link ORDER BY ranking DESC LIMIT $start, $end";
		//echo "<br>selectHotLinks sql=$sql";
		$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

		while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
			$ret[] = $row;
		}

		mysql_free_result($rs);
		//print_r($ret);
		return $ret;
	}

	function selectHotLinksWithinDays($num=10,$withindays=3)
	{
		$start = 0;
		$end = $num;
		$now = new Date();
		$startdate = $now - $withindays;
		
		$sql = "SELECT l.* FROM link l,linksummary ls ORDER BY ls.viewcount DESC WHERE l.sid = ls.linksid AND ls.viewdate > $startdate AND ls.viewdate < $now LIMIT $start, $end";
		//echo "<br>selectHotLinksWithinDays sql=$sql";
		$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

		while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
			$ret[] = $row;
		}

		mysql_free_result($rs);
		//print_r($ret);
		return $ret;

	}


	function deleteUserFavoriteSites($usersid,$chksitesids)
	{
		if($chksitesids){
			$sitesids = implode(",",$chksitesids);
			$sql = "DELETE FROM user2site WHERE usersid=$usersid AND sitesid IN ($sitesids)";
			//echo "<br>deleteUserFavoriteSites sql=$sql";
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
		}
	}

	
	function addUserFavoriteSites($usersid,$chksitesids)
	{
		if($chksitesids){
			$now = time();
			$this->deleteUserFavoriteSites($usersid,$chksitesids);
			foreach($chksitesids as $sid){
				$sql = "INSERT INTO user2site (usersid,relationtype,sitesid,createdDate) VALUES($usersid,0,$sid,'".gmdate('Y-m-d H:i:s', $now)."')";
				$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			}
		}
	}

	function selectUserFavoriteSites($usersid,$pnum=100)
	{
		$start = 0;
		$end = $pnum;
		$sql = "SELECT s.*, u2s.ranking favoriteranking FROM user2site u2s, site s WHERE u2s.usersid=$usersid and u2s.sitesid = s.sid ORDER BY u2s.ranking DESC,createddate DESC LIMIT $start, $end";
		$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());

		while ($row = mysql_fetch_array($rs, MYSQL_ASSOC)) {
			$ret[] = $row;
		}

		mysql_free_result($rs);
		return $ret;
	}

	function findUser2SiteBy($usersid,$sitesid){
		if($usersid && $sitesid){
			$sql = "SELECT s.*,u2s.ranking favoriteranking FROM site s, user2site u2s WHERE u2s.usersid=$usersid AND u2s.sitesid=$sitesid AND u2s.sitesid = s.sid" ;
			$rs = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			$ret = mysql_fetch_array($rs, MYSQL_ASSOC);   
			return $ret;
		}
	}

	function updateUserFavoriteSiteRanking($usersid,$sitesid,$ranking=1){
		//echo "-----updateUserFavoriteSiteTag $usersid,$sitesid,$ranking";
		if($usersid && $sitesid && $ranking){
			//echo "-----2 updateUserFavoriteSiteTag $usersid,$sitesid,$tagname";
			$sql = "UPDATE user2site SET ranking=$ranking WHERE usersid=$usersid AND sitesid=$sitesid";
			$result = mysql_query($sql) or die("Invalid query: $sql " . mysql_error());
			return $result;
		}
	}
 

};
 

?>